This article explains the SSIS Script task and Script component, the scripting tools available in the SQL Server Integration Services (SSIS).
This article is the seventeenth of the SSIS features face-to-face series that aims to compare similar SSIS components to remove any confusion for the data integration developers that use this technology.
Introduction
SQL Server Integration Services is a set of tools for data integration operations. It is used to facilitate the data warehousing process and to solve complex business problems. Integration Services contains features (tasks and components) that allow developers to extract and transform data from various sources such as XML data files, flat files, and relational data sources and then load the data into one or more destinations. The SSIS tasks and components almost support most of the well-known operations needed in the data integration operations, such as:
- Reading from popular data sources: text files, XML, CSV, relational databases, Excel sheets…
- Export data to different data formats
- Insert data into different data repositories
- Operating system tasks
- Performing maintenance tasks (backup, restore, cleanup)
- Data Warehousing operations (Data Quality tasks, Analytical tasks)
- A wide variety of data transformation operations
Besides all these features, one of the main strengths of Integration Services is that it allows the .NET developers to implement any complex logic and utilize libraries from the robust .NET framework or third parties. This capability is available using two main objects: the SSIS Script task and the SSIS Script Component.
In the following sections, we will briefly explain both objects and illustrate some of the use cases for each one.
- Side Note: In SSIS, tasks are executable at the package control flow level, while components are executable within data flows
SSIS Script Task
Based on the official SSIS documentation, SSIS tasks are categorized as the following: Data Flow Task, data preparation tasks, workflow tasks, SQL Server tasks, Analysis Services tasks, maintenance tasks, custom tasks, and scripting tasks.
Scripting Tasks (Mainly the SSIS Script Task) extends the package functionality by using scripts. It performs functions that are not provided by the standard Integration services tasks.
Figure 1 – Script task description in the SSIS toolbox
SSIS Script Task is an effortless task where not much configuration is needed. The initialize a Script task, the user must specify the programming language to be used, the read-only and read-write variables.
Figure 2 – Script task editor
Two programming languages are supported in the SSIS Script task: Visual Basic .NET and C#.
Figure 3 – Choosing the scripting programming language
As mentioned in figure 2, we should click on the “Edit Script” button to open the script editor. The Script task uses Microsoft Visual Studio Tools for Applications (VSTA) as the development environment for the script itself.
-
Side Note: Microsoft Visual Studio Tools for Applications lets you add and run VSTA customizations in applications that are integrated with VSTA
Figure 4 – Script editor
As shown in the image above, there is one class called ScriptMain.cs within the VSTA project. This is the class where we will implement our script. Within the ScriptMain class, we should write our code within the Main function.
Reading and writing to variables
To use a variable within a script, we should ensure that this variable is selected in one of the ReadOnlyVariables and ReadWriteVariables properties, depending on if our code needs to write to the variable.
To read an integer value stored within an SSIS variable we should use the following code:
|
1 |
int integerValue = (int)Dts.Variables["$Package::IntegerVariable"].Value; |
Besides, to write an integer value within an SSIS variable we should use the following code:
|
1 |
Dts.Variables["User::IntegerVariable"].Value = 1; |
As shown in the examples above, variables are accessed from the “Dts” namespace, which allows the script to interact with the external package objects.
Raising events
An SSIS Script task can raise three types of events for logging purposes: Errors, warnings, and information.
To raise an error, we should use the FireError() method:
|
1 |
Dts.Events.FireError(18, "Process Values", "Bad value", "", 0); |
To raise a warning, we should use the FireWarning() method:
|
1 |
Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0); |
To raise information, we should use the FireInformation() method:
|
1 2 |
bool fireAgain = false; Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain); |
Using connection managers
Some types of connection managers can be used within the SSIS Script Task in order to read some of their configuration. As an example, to read a file path from a File Connection Manager, we can use the following code:
|
1 2 |
object sourceFlatFileConnection = Dts.Connections["Source Flat File"].AcquireConnection(Dts.Transaction); string filePath = (string)sourceFlatFileConnection; |
SSIS Script Task use cases
There are several use cases for the use of script tasks, such as listing files, manipulating Excel files, checking for empty files. For more information, Microsoft provides a set of walkthroughs for several examples of using Script Task.
SSIS Script Component
As we mentioned in the introduction, tasks are not intended to run within data flow tasks. They are only used at the control flow level. To extend the data flow tasks functionalities, we should use the SSIS Script Component.
SSIS Script Component configuration is more complex than the SSIS Script task. As a part of the data flow task pipeline, input and output columns metadata must be configured for components.
Figure 5 – Script Component columns configuration
SSIS Script component has three supported functionalities:
- Script as a source: We can use a script component to generate data rows from sources that are not supported in SSIS or if we need to implement a complex logic while loading the data. If used as a source, the Script component supports multiple outputs
- Script as a transformation: We can use a script component to perform complex data transformation. If used as a transformation, the Script component supports one input and multiple outputs
- Script as a destination: We can use a script component as a destination in case we need to insert data into an unsupported destination type in SSIS. If used as a destination, the Script component supports one input
This article will talk briefly about the SSIS Script Component since we already published a detailed article on the SQL Shack website where the component usage and configuration were illustrated.
Reading and writing to variables
Unlike the Script Task, working with variables within a Script Component does not require the “Dts” namespace. The variables will be available as strongly-typed properties of the Variables object:
string filePath = Variables.FilePath;
Besides writing to variables should only do within the PostExecute() method, since the SSIS variables values cannot be modified during the script execution.
|
1 2 3 4 5 |
public override void PostExecute() { base.PostExecute(); Variables.FilePath = ""; } |
Another approach to access variables within a Script Component is using the VariableDispenser property, accessed by calling Me.VariableDispenser. In this case, we are not using the typed and named accessor properties for variables but accessing the variables directly.
SSIS Script Component use cases
In the Microsoft official documentation, they mentioned four purposes to use an SSIS Script Component:
- Apply multiple transformations to data instead of using multiple transformation components in the data flow
- Access business rules in an existing .NET assembly
- Use custom formulas and functions
- Validate column data and skip records that contain invalid data
Besides, to learn more about using an SSIS Script Component as a source, you can refer to the following SQL Shack article: Using the SSIS Script Component as a Data Source.
Summary
Having published a detailed article about the SSIS Script component, this article focused on the SSIS Script task and its use within our Integration Services packages. Besides, we briefly mentioned the Script Component. Besides, we illustrated some of the use cases for both scripting features.
We should keep in mind that the main difference between Script tasks and Script components is that each one is executable at different levels in the SSIS package and each one of them has its use cases even if their names look similar.
Table of contents
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023